package com.bookworld.dao;

import com.bookworld.model.Book;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface BookDao {
    
    @Select("SELECT * FROM books WHERE id = #{id}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    Book findById(Long id);
    
    @Select("SELECT * FROM books WHERE isbn = #{isbn}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    Book findByIsbn(String isbn);
    
    @Select("SELECT * FROM books WHERE active = 1 ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Book> findAllActiveWithPagination(int limit, int offset);
    
    @Select("SELECT * FROM books WHERE active = 1 AND featured = 1 ORDER BY created_at DESC LIMIT #{limit}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Book> findFeaturedBooks(int limit);
    
    @Select("SELECT * FROM books WHERE active = 1 AND category_id = #{categoryId} ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Book> findByCategory(Long categoryId, int limit, int offset);
    
    @Select("SELECT * FROM books WHERE active = 1 AND author_id = #{authorId} ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Book> findByAuthor(Long authorId, int limit, int offset);
    
    @Select("SELECT * FROM books WHERE active = 1 AND stock > 0 ORDER BY sold_count DESC LIMIT #{limit}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Book> findBestSellers(int limit);
    
    @Select("SELECT * FROM books WHERE active = 1 AND (title LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%')) ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "title", column = "title"),
        @Result(property = "isbn", column = "isbn"),
        @Result(property = "description", column = "description"),
        @Result(property = "price", column = "price"),
        @Result(property = "discountPrice", column = "discount_price"),
        @Result(property = "stock", column = "stock"),
        @Result(property = "pages", column = "pages"),
        @Result(property = "language", column = "language"),
        @Result(property = "publishDate", column = "publish_date"),
        @Result(property = "coverImage", column = "cover_image"),
        @Result(property = "publisher", column = "publisher"),
        @Result(property = "edition", column = "edition"),
        @Result(property = "format", column = "format"),
        @Result(property = "weight", column = "weight"),
        @Result(property = "dimensions", column = "dimensions"),
        @Result(property = "categoryId", column = "category_id"),
        @Result(property = "authorId", column = "author_id"),
        @Result(property = "active", column = "active"),
        @Result(property = "featured", column = "featured"),
        @Result(property = "viewCount", column = "view_count"),
        @Result(property = "soldCount", column = "sold_count"),
        @Result(property = "rating", column = "rating"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Book> search(String keyword, int limit, int offset);
    
    @Select("SELECT COUNT(*) FROM books WHERE active = 1")
    int countActive();
    
    @Select("SELECT COUNT(*) FROM books WHERE active = 1 AND category_id = #{categoryId}")
    int countByCategory(Long categoryId);
    
    @Select("SELECT COUNT(*) FROM books WHERE active = 1 AND author_id = #{authorId}")
    int countByAuthor(Long authorId);
    
    @Select("SELECT COUNT(*) FROM books WHERE active = 1 AND (title LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%'))")
    int countBySearch(String keyword);
    
    @Insert("INSERT INTO books (title, isbn, description, price, discount_price, stock, pages, language, publish_date, cover_image, publisher, edition, format, weight, dimensions, category_id, author_id, active, featured, view_count, sold_count, rating, created_at, updated_at) " +
            "VALUES (#{title}, #{isbn}, #{description}, #{price}, #{discountPrice}, #{stock}, #{pages}, #{language}, #{publishDate}, #{coverImage}, #{publisher}, #{edition}, #{format}, #{weight}, #{dimensions}, #{categoryId}, #{authorId}, #{active}, #{featured}, #{viewCount}, #{soldCount}, #{rating}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(Book book);
    
    @Update("UPDATE books SET title=#{title}, isbn=#{isbn}, description=#{description}, price=#{price}, discount_price=#{discountPrice}, " +
            "stock=#{stock}, pages=#{pages}, language=#{language}, publish_date=#{publishDate}, cover_image=#{coverImage}, " +
            "publisher=#{publisher}, edition=#{edition}, format=#{format}, weight=#{weight}, dimensions=#{dimensions}, " +
            "category_id=#{categoryId}, author_id=#{authorId}, active=#{active}, featured=#{featured}, view_count=#{viewCount}, " +
            "sold_count=#{soldCount}, rating=#{rating}, updated_at=#{updatedAt} WHERE id=#{id}")
    void update(Book book);
    
    @Update("UPDATE books SET stock=stock-#{quantity} WHERE id=#{id} AND stock >= #{quantity}")
    int decreaseStock(@Param("id") Long id, @Param("quantity") int quantity);
    
    @Update("UPDATE books SET stock=stock+#{quantity} WHERE id=#{id}")
    void increaseStock(@Param("id") Long id, @Param("quantity") int quantity);
    
    @Update("UPDATE books SET view_count=view_count+1 WHERE id=#{id}")
    void incrementViewCount(Long id);
    
    @Update("UPDATE books SET sold_count=sold_count+#{quantity} WHERE id=#{id}")
    void incrementSoldCount(@Param("id") Long id, @Param("quantity") int quantity);
    
    @Update("UPDATE books SET active=#{active} WHERE id=#{id}")
    void updateActiveStatus(@Param("id") Long id, @Param("active") Boolean active);
    
    @Delete("DELETE FROM books WHERE id = #{id}")
    void delete(Long id);
}
